package com.foreknow.demo17;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcDemo {
    private  Connection conn;
    private ResultSet rs;
    private  Statement stmt;
    /**
     * 获取数据库的连接
     */
    public Connection getConnection(){
        try {
            //1 .加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic8","root","123456");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 查询
     */
    public ResultSet getQuery(String sql) { //select * from User

        List<Guestbook> list = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql); //执行查询并将结果保存到ResultSet结果集
//            list = new ArrayList<>(); //创建一个集合容器
//            while (rs.next()) { //将指针向下移动一行
//                int id = rs.getInt("id"); //获取到当前行的id(列名)
//                String name = rs.getString("name");//获取到当前行的name(列名)
//                String pass = rs.getString("pass");//获取到当前行的pass(列名)
//
//                Guestbook gb = new Guestbook(); //创建对象
//                gb.setId(id); //给对象的属性赋值
//                gb.setName(name);
//                gb.setPass(pass);
//
//                list.add(gb); //将对象保存到集合中
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
         return rs;
    }

    /**
     * 增删改
     */
    public int dml(String sql) {
        int i = 0;
        try {
            stmt = conn.createStatement();
            i = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 释放资源
     */
    public void closed() {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        JdbcDemo demo = new JdbcDemo();
        demo.getConnection(); //连接数据库
        //        ResultSet rs = demo.getQuery("select * from guestbook"); //调用查询方法并将结果保存到ResultSet
        //        while (rs.next()) {
        //            int id = rs.getInt("id");
        //            String name = rs.getString("name");
        //            System.out.println(id + "---" + name);
        //        }
        int isRight = demo.dml("insert into guestbook(name,pass) values('xyz','111111')");
        if (isRight == 1) {
            System.out.println("添加成功......");
        }else {
            System.out.println("添加失败......");
        }

        demo.closed();

//        try {
//            //1 .加载驱动程序
//            Class.forName("com.mysql.jdbc.Driver");
//            //2. 获取连接
//            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/basic8","root","123456");
//            //3. 调用Connection接口中的方法createStatement()获取到Statement对象，作用是将 SQL 语句发送到数据库
//            Statement stmt = conn.createStatement();
//            //4. 操作数据库
//            //            int i = stmt.executeUpdate("insert into guestbook(name,pass,phone,email,title,content,createdtime) " +
//            //                    "values('fender','123456','121212','fender@163.com','test','xxx','2024-08-16')");
//            //            int i = stmt.executeUpdate("delete from guestbook where id=2");
//            //System.out.println(i);
//            ResultSet rs = stmt.executeQuery("select * from guestbook");
//            List<Guestbook> list = new ArrayList<>();
//            while (rs.next()) {
//                int id = rs.getInt("id");
//                String name = rs.getString("name");
//                String pass = rs.getString("pass");
//
//                Guestbook gb = new Guestbook();
//                gb.setId(id);
//                gb.setName(name);
//                gb.setPass(pass);
//
//                list.add(gb);
//            }
//            //遍历集合
//            for (Guestbook gb:list) {
//                System.out.println(gb.getName());
//            }
//            //5. 关闭资源
//            rs.close();
//            stmt.close();
//            conn.close();
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }

    }
}
